﻿using System;
using System.Data;
using System.Diagnostics.Eventing.Reader;
using Titan.DAS.Client.Data;
using Titan.FIS.Enum;

namespace Titan.FIS.LMS.DataOperate
{
    public class DataQuery
    {
        private static string _strSql = string.Empty;

        /// <summary>
        /// 按条件查询化验数据信息
        /// </summary>
        public static string DoQueryLmsResultInfo(LaboratoryType labType, string strStartTime, string strEndTime, string strAsTestCode,
                                                     string strAsTypeText, string strAsType, string strState, ref DataTable tabSource)
        {
            var errMsg = string.Empty;
            var strCjColumn = @"    COLLECTION_ID,
                                    TEST_CODE,
                                    to_char(INSTRUMENT) as INSTRUMENT,
								   CASE
									 WHEN STATUS = 0 THEN
                                            '未使用'
                                            WHEN STATUS = 1 THEN
                                            '已采用'
                                            WHEN STATUS = 2 THEN
                                            '已弃用'
                                            ELSE
									  '未知'
								   END AS STATUS,
                                       AUTO_NO,
                                       TEST_VALUE,
                                       to_char(COLLECTION_TIME,'yyyy-mm-dd hh24:mi:ss') as COLLECTION_TIME,
                                       TEST_SAMPLEWEIGHT,
								   case
									 when trunc(COLLECTION_TIME) < trunc(TEST_TIME) then
									  to_char(COLLECTION_TIME, 'yyyy-mm-dd') ||
									  to_char(TEST_TIME, ' hh24:mi:ss')
									 else
									  to_char(TEST_TIME, 'yyyy-mm-dd hh24:mi:ss')
								   end as TEST_TIME                              
                                       ";
            var strReportColumn = @" TEST_CODE,
                                        STYPE_STR AS STYPE,
                                        TEST_TYPE_STR AS TEST_TYPE,
                                        TEST_MODE_STR AS TEST_MODE,
                                        TEST_STATE_STR AS TEST_STATE,
                                        to_char(UPDATE_TIME,'yyyy-MM-dd HH24:mi:ss') AS UPDATE_TIME,
                                        VAD,
                                        VAR,
                                        VD, 
                                        VDAF,                                     
                                        MAD,
                                        MT,
                                        AAR,
                                        AAD,
                                        AD,
                                        FCAD,
                                        HAD,
                                        STAR,
                                        STAD,
                                        STD,   
                                        CRC,                             
                                        QBAD,
                                        QBD,
                                        QGRAD,
                                        QGRD,
                                        QNETAR_MJ*1000 AS QNETAR_KJ,
                                        QNETAR_KCAL,
                                        DT,
                                        ST,
                                        HT,
                                        FT
                                        ";
            var strReportRCColumn = @"  A.TEST_CODE,
                                        A.STYPE_STR AS STYPE,
                                        A.TEST_TYPE_STR AS TEST_TYPE,
                                        A.TEST_MODE_STR AS TEST_MODE,
                                        A.TEST_STATE_STR AS TEST_STATE,
										A.AUDIT_STATE,
										A.AUDIT_REMARK,
                                        to_char(A.UPDATE_TIME,'yyyy-MM-dd HH24:mi:ss') AS UPDATE_TIME,
                                        A.VAD_STR AS VAD,
                                        A.VAR,
                                        A.VD, 
                                        A.VDAF,                                     
                                        A.MAD_STR AS MAD,
                                        A.MT_STR AS MT,
                                        A.AAR,
                                        A.AAD_STR AS AAD,
                                        A.AD,
                                        A.FCAD,
                                        A.HAD,
                                        A.STAR,
                                        A.STAD_STR AS STAD,
                                        A.STD,   
                                        A.CRC,                             
                                        A.QBAD_STR AS QBAD,
                                        A.QBD,
                                        A.QGRAD,
                                        A.QGRD,
                                        A.QNETAR_MJ*1000 AS QNETAR_KJ,
                                        A.QNETAR_KCAL,
                                        A.G,
                                        A.Y,
                                        A.X,
                                        A.a,
                                        A.b,
                                        A.CRI,
                                        A.CSR,
                                        A.Small,
                                        A.Medium,
                                        A.Large,
                                        A.TOD,
                                        A.Rmax,
                                        A.MF,
                                        A.M40,
                                        A.M10,
                                        A.JTXXLarge,
                                        A.JTXLarge,
                                        A.JTLarge,
                                        A.JTMedium,
                                        A.JTSmall,
                                        A.SMEDIUN
                                        ";
            if (strAsTypeText == "化验结果数据")
            {
                _strSql = string.Format(@"select {0}, B.BATCH_TYPE, B.MATERIEL_TYPE_NAME, B.MATERIEL_ID
										   from V_LMS_RESULT A
										   LEFT JOIN v_pl_all_details B
											 ON A.TEST_CODE = B.TEST_CODE
											AND A.STYPE = 0
											AND A.TEST_TYPE = 0 
											where 1 = 1", labType == LaboratoryType.RC ? strReportRCColumn : strReportColumn);
                if (!string.IsNullOrEmpty(strAsTestCode)) _strSql += " and A.TEST_CODE like '%" + strAsTestCode + "%'";
                if (!string.IsNullOrEmpty(strState)) _strSql += "and A.TEST_STATE_STR = '" + strState + "'";
                if (!string.IsNullOrEmpty(strStartTime)) _strSql += " and A.UPDATE_TIME >= to_date('" + strStartTime + "','yyyy-mm-dd hh24:mi:ss')";
                if (!string.IsNullOrEmpty(strEndTime)) _strSql += " and A.UPDATE_TIME <= to_date('" + strEndTime + "','yyyy-mm-dd hh24:mi:ss') ";
                //var lab = labType == LaboratoryType.RC ? "((LABORATORY = 4 AND DT!=0) OR LABORATORY =0)" : "LABORATORY = 2";
                //_strSql += $" and {lab}";
                _strSql += " order by A.UPDATE_TIME,A.TEST_TYPE desc";
            }
            else
            {
                _strSql = string.Format(@"select {0} from LMS_TESTDATA_COLLECTION where INDEX_ID = {1} ", strCjColumn, strAsType);
                if (!string.IsNullOrEmpty(strAsTestCode)) _strSql += " and TEST_CODE like '%" + strAsTestCode + "%'";
                if (!string.IsNullOrEmpty(strStartTime)) _strSql += " and COLLECTION_TIME >= to_date('" + strStartTime + "','yyyy-mm-dd hh24:mi:ss')";
                if (!string.IsNullOrEmpty(strEndTime)) _strSql += " and COLLECTION_TIME <= to_date('" + strEndTime + "','yyyy-mm-dd hh24:mi:ss')";
                //if (labType == LaboratoryType.RC)
                //    _strSql += $" and INSTRUMENT IN ({(int)LaboratoryInstrument.HRR_A})";
                //if (labType == LaboratoryType.RL)
                //    _strSql += $" and INSTRUMENT NOT IN ({(int)LaboratoryInstrument.HRR_A})";
                _strSql += " order by TEST_TIME,AUTO_NO";
            }

            var modelQuery = DataHelper.Instance.QuerySql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                if (modelQuery.RSource != null && modelQuery.RSource.Tables.Count > 0 && modelQuery.RSource.Tables[0].Rows.Count > 0)
                    tabSource = modelQuery.RSource.Tables[0];
                else
                    errMsg = "查询完毕，未找到相关数据，请更换条件后查询！";
            }
            else
                errMsg = string.Format("查询化验数据信息失败！\r\n错误代码:{0}  错误信息:{1}", modelQuery.RStatuCode, modelQuery.RErrInfo + modelQuery.RStatuDesc);
            return errMsg;
        }

        /// <summary>
        /// 查询物料对应的化验复检指标
        /// </summary>
        public static string DoQueryReTestIndex(string strIsTest, string strIsCheck, ref DataTable tabSource)
        {
            var errMsg = string.Empty;
            _strSql = string.Format(@"select A.MATERIEL_ID,
										   A.MATERIEL_NAME,
										   A.MATERIEL_TYPE_ID,
										   A.LMS_TEMPLATE_ID,
										   A.INDEX_ID,
										   A.INDEX_CODE,
										   A.INDEX_DISNAME,
										   A.VALUE_BASE,
										   A.VALUE_MIN,
										   A.VALUE_MAX,
										   A.VALUE_TEST,
										   A.VALUE_CHECK,
										   A.VALUE_TYPE,
										   A.VALUE_SHOW from v_materiel_lms_recheck A where 1 = 1 ");
            if (!string.IsNullOrEmpty(strIsTest)) _strSql += " and A.VALUE_TEST = " + strIsTest;
            if (!string.IsNullOrEmpty(strIsCheck)) _strSql += " and A.VALUE_CHECK = " + strIsCheck;
            var modelQuery = DataHelper.Instance.QuerySql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                if (modelQuery.RSource != null && modelQuery.RSource.Tables.Count > 0 && modelQuery.RSource.Tables[0].Rows.Count > 0)
                    tabSource = modelQuery.RSource.Tables[0];
                else
                    errMsg = "查询完毕，未找到化验和复检指标相关数据！";
            }
            else
                errMsg = string.Format("查询化验和复检指标信息失败！\r\n错误代码:{0}  错误信息:{1}", modelQuery.RStatuCode, modelQuery.RErrInfo + modelQuery.RStatuDesc);
            return errMsg;
        }

        /// <summary>
        /// 查询化验指标是否已确认
        /// </summary>
        public static string DoDeleteTestData(string strAsTestCode,int indexId,string testValue)
        {
            if(string.IsNullOrEmpty(strAsTestCode))
                return "化验码不能为空";
            if (indexId<=0)
                return "请输入要删除的数据";
            if (string.IsNullOrEmpty(testValue))
                return "请输入要删除的数据";
            _strSql = $"DELETE FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{strAsTestCode}' AND INDEX_ID = {indexId} AND TEST_VALUE='{testValue}' ";
            var modelQuery = DataHelper.Instance.ExecSql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                return "success";
            }
            else
                return $"操作失败{modelQuery.RStatuCode}，{modelQuery.RStatuDesc}";
        }

        /// <summary>
        /// 查询化验指标是否已确认
        /// </summary>
        public static string DoQueryLmsIndexState(string strAsTestCode, int iCType, int iSType, int iMType, int index, ref bool bConfirmed)
        {
            var errMsg = string.Empty;
            bConfirmed = false;
            if (iCType == 0 && iSType == 0 && (iMType == 0 || iMType == 1))//系统内首次化验，常规类型只能存在一条化验数据
                _strSql = string.Format(@"SELECT ROWNUM AS NUM FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND TEST_TYPE = 0 AND STYPE = 0 AND TEST_MODE <> 2", strAsTestCode, index);
            else//复检样、再现性、非常规、第三方，可以存在多条化验数据
            {
                if (iCType == 2)//复检样可以存在未审核的多条结果
                    _strSql = string.Format(@"SELECT ROWNUM AS NUM FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND STYPE = '{2}'  AND TEST_MODE <> 2 AND TEST_STATE = 1", strAsTestCode, index, iSType);
                else// 其他样可以存在已审核的多条结果
                    _strSql = string.Format(@"SELECT ROWNUM AS NUM FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND STYPE = '{2}' AND TEST_STATE = 0", strAsTestCode, index, iSType);

            }
            var modelQuery = DataHelper.Instance.QuerySql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                if (modelQuery.RSource != null && modelQuery.RSource.Tables.Count > 0 && modelQuery.RSource.Tables[0].Rows.Count > 0)
                {
                    bConfirmed = true;
                }
            }
            else
                errMsg = string.Format("查询化验编码【{0}】的化验指标是否已确认信息失败！\r\n错误代码:{1}  错误信息:{2}", strAsTestCode, modelQuery.RStatuCode, modelQuery.RErrInfo + modelQuery.RStatuDesc);
            return errMsg;
        }

        public static string DoQueryLmsIndexStateMad(string strAsTestCode, int iCType, int iSType, int iMType, int index, ref bool bConfirmed, ref decimal madValue)
        {
            var errMsg = string.Empty;
            bConfirmed = false;
            if (iCType == 0 && iSType == 0 && (iMType == 0 || iMType == 1))//系统内首次化验，常规类型只能存在一条化验数据
                _strSql = string.Format(@"SELECT ROWNUM AS NUM,TEST_VALUE FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND TEST_TYPE = 0 AND STYPE = 0 AND TEST_MODE <> 2", strAsTestCode, index);
            else//复检样、再现性、非常规、第三方，可以存在多条化验数据
            {
                if (iCType == 2)//复检样可以存在未审核的多条结果
                    _strSql = string.Format(@"SELECT ROWNUM AS NUM,TEST_VALUE FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND STYPE = '{2}'  AND TEST_MODE <> 2 AND TEST_STATE = 1", strAsTestCode, index, iSType);
                else// 其他样可以存在已审核的多条结果
                    _strSql = string.Format(@"SELECT ROWNUM AS NUM,TEST_VALUE FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = '{0}' AND INDEX_ID = '{1}' AND STYPE = '{2}' AND TEST_STATE = 0", strAsTestCode, index, iSType);

            }
            var modelQuery = DataHelper.Instance.QuerySql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                if (modelQuery.RSource != null && modelQuery.RSource.Tables.Count > 0 && modelQuery.RSource.Tables[0].Rows.Count > 0)
                {
                    bConfirmed = true;
                    madValue = decimal.Parse(modelQuery.RSource.Tables[0].Rows[0]["TEST_VALUE"].ToString());
                }
            }
            else
                errMsg = string.Format("查询化验编码【{0}】的化验指标是否已确认信息失败！\r\n错误代码:{1}  错误信息:{2}", strAsTestCode, modelQuery.RStatuCode, modelQuery.RErrInfo + modelQuery.RStatuDesc);
            return errMsg;
        }

        /// <summary>
        /// 查询化验样品类型（煤，焦炭）
        /// </summary>
        public static string DoQueryTestSampleType(string strAsTestCode, ref int testSampleType)
        {
            var errMsg = string.Empty;
            _strSql = string.Format(@"SELECT BATCH_SAMPLE_TYPE FROM V_PL_ALL_DETAILS WHERE TEST_CODE = '{0}'", strAsTestCode);
            var modelQuery = DataHelper.Instance.QuerySql(Common.Common.UserInfo.UserName, Common.Common.UserInfo.UserPwd, _strSql);
            if (modelQuery.RSuccess)
            {
                if (modelQuery.RSource != null && modelQuery.RSource.Tables.Count > 0 && modelQuery.RSource.Tables[0].Rows.Count > 0)
                {
                    if (modelQuery.RSource.Tables[0].Rows[0]["BATCH_SAMPLE_TYPE"].ToString() == "1")
                        testSampleType = 1;
                }
            }
            else
                errMsg = string.Format("查询化验编码【{0}】的样品批次类型信息失败！\r\n错误代码:{1}  错误信息:{2}", strAsTestCode, modelQuery.RStatuCode, modelQuery.RErrInfo + modelQuery.RStatuDesc);
            return errMsg;
        }


    }
}
